package window;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.ResourceBundle;

import org.apache.commons.lang.StringUtils;



public class ReadTable {

	private static final ResourceBundle bundle = ResourceBundle.getBundle("database");

	private Connection conn;
	private Statement stmt;
	private String sql;
	private ResultSet rs;

	public static String DATABASE_TYPE = bundle.getString("database_type");

	public static String DIVER_NAME = bundle.getString("diver_name");

	public static String URL = bundle.getString("url");

	public static String USERNAME = bundle.getString("username");

	public static String PASSWORD = bundle.getString("password");

	public static String DATABASE_NAME = bundle.getString("database_name");

	private static List<ColumnBeans> list = null;

	  
	/**
	 * 读取表数据
	 * 
	 * @param dataBase
	 *            数据库名
	 * @param tableName
	 *            表名
	 * @return
	 * 
	 */
	public Collection<Map<String, String>> readData(String tableName) {
		Collection<Map<String, String>> properties = new HashSet<Map<String, String>>();
		try {
			Class.forName(DIVER_NAME);
			conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
			stmt = conn.createStatement(1005, 1007);
			
			if("mysql".equals(DATABASE_TYPE)){
			
				DatabaseMetaData dbmd = conn.getMetaData();
				rs = dbmd.getColumns(conn.getCatalog(), null, tableName, null);
				while (rs.next()) {
					Map<String, String> map = new HashMap<String, String>();
					map.put("fieldName",formatFieldCapital(rs.getString("COLUMN_NAME")));
					map.put("fieldname",formatField(rs.getString("COLUMN_NAME")));
					map.put("fieldDBType",formatDataType(rs.getString("TYPE_NAME").toLowerCase(),rs.getString("COLUMN_SIZE"),rs.getString("DECIMAL_DIGITS")));
					map.put("fieldType",formatJavaType(formatDataType(rs.getString("TYPE_NAME").toLowerCase(),rs.getString("COLUMN_SIZE"),rs.getString("DECIMAL_DIGITS"))));
					map.put("remarks", formatField(rs.getString("REMARKS")));

					properties.add(map);
				}
			}else if("oracle".equals(DATABASE_TYPE)){

				queryFieldName(conn,tableName);
//				
			      //String sql="select * from user_col_comments t where t.table_name ='its_seat_recommend_info'";
				  String sql="select colstable.column_name column_name, colstable.data_type data_type, commentstable.comments column_comment, colstable.Data_Precision column_precision, colstable.Data_Scale column_scale,colstable.Char_Length,colstable.nullable from user_tab_cols colstable  inner join user_col_comments commentstable  on colstable.column_name = commentstable.column_name  where colstable.table_name = commentstable.table_name  and colstable.table_name ='its_seat_recommend_info'";
			      System.out.println(sql);
			      rs=stmt.executeQuery(sql);
			      while (rs.next()) {
			    	  System.out.println("1111111");
					System.out.println(rs.getString("data_type"));
					System.out.println(rs.getString("column_name"));
					System.out.println(rs.getString("column_comment"));
					
				}
//			      ResultSetMetaData rsm = rs.getMetaData();
//			      int columnIndex = rsm.getColumnCount();
//			      for (int i = 1; i <= columnIndex; ++i) {
//			    	   Map<String, String> map = new HashMap<String, String>();  
////		               map.put("fieldName", formatFieldCapital(rsm.getColumnName(i)));  
////		               map.put("fieldType", formatDataType(rsm.getColumnTypeName(i).toLowerCase(),"0","0"));  
////		               
////		               map.put("remarks", formatField((String)map.get(rsm.getColumnName(i))));  
////		               
////		               properties.add(map);  
//	
//		               System.out.println(rsm.getSchemaName(i));
//		               System.out.println(rsm.getColumnClassName(i));
//		               System.out.println(rsm.getColumnLabel(i));
//		               System.out.println(rsm.getColumnName(i));
//		               System.out.println(rsm.getColumnTypeName(i));
//		               System.out.println(rsm.getScale(i));
//		               System.out.println(rsm.getTableName(i));
//		               System.out.println(rsm.isNullable(i));
//		               System.out.println("********************************");
//
////			        ColumnBeans columnBeans = new ColumnBeans();
////			        columnBeans.setComments((String)map.get(rsm.getColumnName(i)));
////			        columnBeans.setName(formatTableColumnToClassField(rsm.getColumnName(i)));
////			        columnBeans.setType(formatTableTypeToJavaType(rsm.getColumnTypeName(i)));
////			        columnBeans.setCName(rsm.getColumnName(i));
////			        columnBeans.setCType(rsm.getColumnTypeName(i));
////			        columnBeans.setLeng(rsm.getColumnDisplaySize(i));
////			        list.add(columnBeans);
//			      }
//			      
//				rs = stmt.executeQuery("select COLUMN_NAME,DATA_TYPE,DATA_PRECISION,DATA_SCALE,NULLABLE from user_tab_columns where table_name = '"+tableName+"'");//得到查询结果,一个数据集 
//				
//				 ResultSetMetaData rsm = rs.getMetaData();
//		         int columnIndex = rsm.getColumnCount();
//		         for (int i = 1; i <= columnIndex; ++i) {
//	                Map<String, String> map = new HashMap<String, String>();  
//	                map.put("fieldName", formatFieldCapital(rsm.getColumnName(i)));  
//	                map.put("fieldType", formatDataType(rsm.getColumnTypeName(i).toLowerCase(),"0","0"));  
//	                map.put("remarks", formatField(rs.getString("REMARKS")));  
//	                properties.add(map);  
//
//	                System.out.println(formatFieldCapital(rs.getString("COLUMN_NAME")));
//	             }  
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				if (rs != null) {
					rs.close();
				}
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
		return properties;
	}

	
	 private static void queryFieldName(Connection connection, String tableName) throws SQLException
	  {
	    Map map = queryFiledcomments(connection, tableName);
	    list=new ArrayList<ColumnBeans>();

	    String sql = "select * from " + tableName + " where 1<0";
	    PreparedStatement pre = null;
	    ResultSet rs = null;
	    try {
	      pre = connection.prepareStatement(sql);
	      rs = pre.executeQuery();
	      ResultSetMetaData rsm = rs.getMetaData();
	      int columnIndex = rsm.getColumnCount();
	      for (int i = 1; i <= columnIndex; ++i) {
	        ColumnBeans columnBeans = new ColumnBeans();
	        columnBeans.setComments((String)map.get(rsm.getColumnName(i)));
	        columnBeans.setName(formatFieldCapital(rsm.getColumnName(i)));
	        columnBeans.setType(formatDataType(rsm.getColumnTypeName(i),"",""));
	        columnBeans.setCName(rsm.getColumnName(i));
	        columnBeans.setCType(rsm.getColumnTypeName(i));
	        columnBeans.setLeng(rsm.getColumnDisplaySize(i));
	        
	        System.out.println(columnBeans.getComments());
	        System.out.println(columnBeans.getName());
	        System.out.println(columnBeans.getType());
	        System.out.println(columnBeans.getCName());
	        System.out.println(columnBeans.getCType());
	        System.out.println(columnBeans.getLeng());
	        System.out.println("");

	        list.add(columnBeans);
	      }

	    }
	    catch (RuntimeException e)
	    {
	      throw e;
	    } catch (SQLException e) {
	      throw e;
	    } finally {
	    }
	  }

	 
	 private static Map<String, String> queryFiledcomments(Connection connection, String tableName)
			    throws SQLException
			  {
			    String sql = "";
			    if ("oracle".equals(DATABASE_TYPE))
			      sql = "select * from user_col_comments t where t.table_name = '" + tableName.toUpperCase() + "'";
			    else if ("mysql".equals(DATABASE_TYPE)) {
			      sql = "show full FIELDS from " + tableName.toUpperCase();
			    }
			    PreparedStatement pre = null;
			    ResultSet rs = null;
			    Map map = new HashMap();
			    try {
			      pre = connection.prepareStatement(sql);
			      rs = pre.executeQuery();
			      while (rs.next()) {
			        if ("oracle".equals(DATABASE_TYPE))
			          map.put(rs.getString("column_name"), rs.getString("comments"));
			        else if ("mysql".equals(DATABASE_TYPE)) {
			          map.put(rs.getString("field"), rs.getString("comment"));
			        }
			      }
			      return map;
			    } catch (SQLException e) {
			    }
			    finally {
			    	
			    }
				return map;
			  }
	 
	
    /**
     * 查询数据库中所有表
     * @return
     * @throws SQLException
     */
	public List<String> readAllTableNames() throws SQLException {
		List tableNames = new ArrayList(0);
		try {
			Class.forName(DIVER_NAME);
			this.conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
			this.stmt = this.conn.createStatement(1005, 1007);
			
			if (DATABASE_TYPE.equals("mysql")) {
				this.sql = MessageFormat
						.format("select distinct table_name from information_schema.columns where table_schema = {0}",
								new Object[] { getV(DATABASE_NAME) });
			}

			if (DATABASE_TYPE.equals("oracle")) {
				this.sql = " select distinct colstable.table_name as  table_name from user_tab_cols colstable";
			}

			if (DATABASE_TYPE.equals("postgresql")) {
				this.sql = "SELECT distinct c.relname AS  table_name FROM pg_class c";
			}

			if (DATABASE_TYPE.equals("sqlserver")) {
				this.sql = "select distinct c.name as  table_name from sys.objects c ";
			}

			this.rs = this.stmt.executeQuery(this.sql);
			while (this.rs.next()) {
				String tableName = this.rs.getString(1);
				tableNames.add(tableName);
			}
		} catch (Exception e) {
			e.printStackTrace();
			try {
				if (this.stmt != null) {
					this.stmt.close();
					this.stmt = null;
					System.gc();
				}
				if (this.conn != null) {
					this.conn.close();
					this.conn = null;
					System.gc();
				}
			} catch (SQLException SQLE) {
				throw SQLE;
			}
		} finally {
			try {
				if (this.stmt != null) {
					this.stmt.close();
					this.stmt = null;
					System.gc();
				}
				if (this.conn != null) {
					this.conn.close();
					this.conn = null;
					System.gc();
				}
			} catch (SQLException e) {
				throw e;
			}
		}
		return tableNames;

	}

	/**
	 * 查看表是否存在
	 * @param tableName
	 * @return
	 */
	public boolean checkTableExist(String tableName) {
		try {
			
			System.out.println("数据库驱动: " + DIVER_NAME);
			
			Class.forName(DIVER_NAME);
			this.conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
			this.stmt = this.conn.createStatement(1005, 1007);
			
			if (DATABASE_TYPE.equals("mysql")) {
				this.sql = "select column_name,data_type,column_comment,0,0 from information_schema.columns where table_name = '"
						+ tableName.toUpperCase()
						+ "'"
						+ " and table_schema = '" + DATABASE_NAME + "'";
			}

			if (DATABASE_TYPE.equals("oracle")) {
				this.sql = " select count(1) from all_tables where owner = '"+USERNAME+"' and table_name = '"+ tableName.toUpperCase() + "'";
			}

			if (DATABASE_TYPE.equals("postgresql")) {
				this.sql = MessageFormat
						.format("SELECT a.attname AS  field,t.typname AS type,col_description(a.attrelid,a.attnum) as comment,null as column_precision,null as column_scale,null as Char_Length,a.attnotnull  FROM pg_class c,pg_attribute  a,pg_type t  WHERE c.relname = {0} and a.attnum > 0  and a.attrelid = c.oid and a.atttypid = t.oid  ORDER BY a.attnum ",
								new Object[] { getV(tableName.toLowerCase()) });
			}
			if (DATABASE_TYPE.equals("sqlserver")) {
				this.sql = MessageFormat
						.format("select cast(a.name as varchar(50)) column_name,  cast(b.name as varchar(50)) data_type,  cast(e.value as varchar(200)) comment,  cast(ColumnProperty(a.object_id,a.Name,'''Precision''') as int) num_precision,  cast(ColumnProperty(a.object_id,a.Name,'''Scale''') as int) num_scale,  a.max_length,  (case when a.is_nullable=1 then '''y''' else '''n''' end) nullable   from sys.columns a left join sys.types b on a.user_type_id=b.user_type_id left join sys.objects c on a.object_id=c.object_id and c.type='''U''' left join sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.column_id and e.class=1 where c.name={0}",
								new Object[] { getV(tableName.toLowerCase()) });
			}

			System.out.println(sql);
			this.rs = this.stmt.executeQuery(this.sql);
			this.rs.last();
			int fieldNum = this.rs.getRow();
			if (fieldNum > 0)
				return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return false;
	}

	/**
	 * 数据库字段格式化后变小写
	 * @param field
	 * @return
	 */
	public static String formatField(String field) {
		String[] strs = field.split("_");
		field = "";
		int m = 0;
		for (int length = strs.length; m < length; ++m) {
			if (m > 0) {
				String tempStr = strs[m].toLowerCase();
				tempStr = tempStr.substring(0, 1).toUpperCase()
						+ tempStr.substring(1, tempStr.length());
				field = field + tempStr;
			} else {
				field = field + strs[m].toLowerCase();
			}
		}
		return field;
	}

	/**
	 * 数据库字段格式化后变大写
	 * @param field
	 * @return
	 */
	public static String formatFieldCapital(String field) {
		String[] strs = field.split("_");
		field = "";
		int m = 0;
		for (int length = strs.length; m < length; ++m) {
			if (m > 0) {
				String tempStr = strs[m].toLowerCase();
				tempStr = tempStr.substring(0, 1).toUpperCase()
						+ tempStr.substring(1, tempStr.length());
				field = field + tempStr;
			} else {
				if(strs[0].indexOf("Impl")<0){
					field = field + strs[0].toLowerCase();
				}else{
					field= strs[0];
				}
			}
		}
		field = field.substring(0, 1).toUpperCase() + field.substring(1);
		return field;
	}

	/**
	 * 数据库类型转成java完整类名
	 * @param dataType
	 * @param precision
	 * @param scale
	 * @return
	 */
	private static String formatDataType(String dataType, String precision,String scale) {
		if (dataType.contains("char") || dataType.contains("text"))
			dataType = "java.lang.String";
		else if (dataType.contains("int"))
			dataType = "java.lang.Integer";
		else if (dataType.contains("float"))
			dataType = "java.lang.Float";
		else if (dataType.contains("double"))
			dataType = "java.lang.Double";
		else if (dataType.contains("number")) {
			if ((StringUtils.isNotBlank(scale))
					&& (Integer.parseInt(scale) > 0))
				dataType = "java.math.BigDecimal";
			else if ((StringUtils.isNotBlank(precision))
					&& (Integer.parseInt(precision) > 10))
				dataType = "java.lang.Long";
			else
				dataType = "java.lang.Integer";
		} else if (dataType.contains("decimal"))
			dataType = "BigDecimal";
		else if (dataType.contains("date"))
			dataType = "java.util.Date";
		else if (dataType.contains("time")) {
			dataType = "java.util.Date";
		} else if (dataType.contains("blob"))
			dataType = "byte[]";
		else if (dataType.contains("clob"))
			dataType = "java.sql.Clob";
		else if (dataType.contains("numeric"))
			dataType = "BigDecimal";
		else {
			dataType = "java.lang.Object";
		}
		return dataType;
	}
	
	/**
	 * 数据库类型转成java类
	 * @param dataType
	 * @param precision
	 * @param scale
	 * @return
	 */
	private String formatJavaType(String type){
		if(type.indexOf(".")>0){
			type=type.substring(type.lastIndexOf(".")+1,type.length());
			return type;
		}
		return type;
	}

	/**
	 * 根据包名获取对应的路径名
	 * 
	 * @param root
	 *            根路径
	 * @param pack
	 *            包名
	 * @return
	 */
	public String genPackStr(String root, String pack) {
		String result = root;
		String[] dirs = pack.split("\\.");
		for (String dir : dirs) {
			result += "/" + dir;
		}
		System.out.println("***********"+result);
		return result;
	}

	public String getV(String s) {
		return "'" + s + "'";
	}
	
	public static void main(String[] args) {
		System.out.println(new ReadTable().formatJavaType("BigDecimal"));

		System.out.println(new ReadTable().formatJavaType("java.lang.Object"));
	}
}
